In typical database systems, users write, update and retrieve information by submitting statements to a database application. To be correctly processed, the commands must comply with the database language that is supported by the database application. One popular database language is known as Structured Query Language (“SQL”).
Operations that modify data stored in a database are referred to as DML (“data manipulation language”) operations. DML operations include inserting a row, updating a row, and deleting a row. The subset of SQL commands that specify DML operations are referred to as DML commands. Examples of DML commands in SQL include Insert, Update, Upsert, and Delete.
Many database systems are multi-processing systems. Multi-processing systems are typically partitioned into nodes, where each node may contain multiple processors executing multiple concurrent processes. To fully utilize the computing power of a multi-processing system, a database system may divide a large processing task (“parent task”) into smaller subtasks, which may then be distributed to multiple processes running on one or more processing nodes. Because the subtasks are being performed in parallel, the processing required by the parent task can be completed much faster than if the processing is performed by a single process.
A parent task that is divided into smaller subtasks that are executed by multiple processes is referred to herein as a distributed operation. The number of processes that are assigned to perform a distributed operation is referred to as the degree of parallelism. In general, a task may be performed more efficiently by increasing the degree of parallelism, at least up to a particular threshold.
At least part of a parent task is divided in smaller subtasks during a process referred to as execution planning. When a database server receives a request to execute a database statement, the database server generates an execution plan and then executes the plan. The process of generating the execution plan is also referred to as the execution planning phase. Execution planning may include creating subtasks for executing the database statement and assigning processes to execute a portion of the execution plan.
Unfortunately, the degree of parallelism that may be achieved for a distributed DML operation is limited. A reason for this limitation is that a distributed DML operation must be divided and assigned to processes in a way that avoids undue contention for locks and other resources used to manage concurrent access to units of storage in which data is stored, as shall be explained in greater detail below.